const query = require('../db');
const util = require('../util');
const  nodeExcel = require('excel-export');
module.exports={
    // 创建签到
    addSign(data,callback){
        // let create_time = new Date().toLocaleString();
        let sql = `INSERT INTO sign(\`desc\`,title,start_time,end_time,creater,longitude,latitude,class_id,protocol,remake,addRessrange)
        VALUES ('${data.desc}','${data.title}','${data.start_time}','${data.end_time}','${data.creater}',${data.longitude},${data.latitude},${data.class_id},'${data.protocol}','${data.remake}',${data.addRessrange})`;
        query(sql,(err,data)=>{
            if(err){
                console.log(err);
            }else{
                callback(data);
            }
        })
    },
    //查询指定签到，或者所有签到
    getSignList(sign_id,callback){
        let sql = null;
        if(sign_id){
             sql = `SELECT * FROM sign WHERE sign_id='${sign_id}'`;
        }else{
             sql ='SELECT * FROM sign';
        }
        query(sql,(err,data)=>{
            if(err){
                console.log(err);
            }else{
                callback(data);
            }
        })
    },
    //根据班级guid查询签到
    getStudentList(class_id,callback){
        let sql = `SELECT * FROM sign WHERE class_id=${class_id} AND status=1 UNION SELECT * FROM sign WHERE  ISNULL(class_id) AND status=1`;
        query(sql,(err,data)=>{
            callback(err,data);
        })
    },
    //根据创建者学号查询签到
    getCreaterSigns(creater,callback){
      let sql = `SELECT * FROM sign WHERE creater='${creater}' AND status=1 `;
      query(sql,(err,data)=>{
          if(err){
              console.log(err);
          }else {
              callback(data);
          }
      })
    },
    //根据签到ID来删除签到
    deleteSignByid(sign_id,callback){
        let sql =`UPDATE sign SET  \`status\`=0 WHERE sign_id =${sign_id}`;
        query(sql,(err,data)=>{
            if(err){
                console.log(err);
            }else {
                callback(data);
            }
        })
    },
    //修改签到
    modifySignByid(data,callback){
        let create_time = new Date().toLocaleString();
         let sql = `UPDATE sign SET  \`desc\`='${data.desc}',title='${data.title}',start_time='${create_time}',end_time='${data.end_time}',longitude=${data.longitude},latitude=${data.latitude},protocol='${data.protocol}'  WHERE sign_id=${data.sign_id}`;
         query(sql,(err,data)=>{
              if(err){
                  console.log(err);
              }else {
                  callback(data);
              }
         })
    },
    //学生签到
    studentSign(data,callback){
        let create_time = new Date().toLocaleString();
        //查询该学生是否签到
        let sql1 = `SELECT * FROM user_in_sign WHERE sign_id=${data.sign_id} AND user_id='${data.user_id}'`;
        query(sql1,(err,res)=>{
            if(res[0]){
               let err = new Error('学生已经签到');
               callback(err,res)
           }else {
               let sql =`INSERT INTO user_in_sign(sign_time,sign_id,class_id,class_name,user_id,user_name,sign_latitude,sign_longitude)
            VALUES('${create_time}',${data.sign_id},${data.class_id},'${data.class_name}','${data.user_id}','${data.user_name}',${data.sign_latitude},${data.sign_longitude})`;
               query(sql,(err,data)=>{
                   callback(err,data);
               })
           }
        });

    },
    // 将学生签到的表格导出
    exportSignExcel(sign_id,callbck){
        //首先根据签到id去获取签到信息
        let sql = `SELECT * FROM sign WHERE sign_id=${sign_id}`;
        query(sql,(err,data)=>{
            if(err){
                console.log(err);
            } else {
                //签到信息
                let signInfo = data[0];
                sql = `SELECT *  FROM user_in_sign WHERE sign_id=${sign_id}`;
                query(sql,(err,data)=>{
                    if(err){
                        console.log(err);
                    }else {
                        //签到学生信息
                        let contentInfo = data;
                        let conf ={};// 表格配置信息
                        conf.stylesXmlFile = "module/styles.xml";
                        let confs = [];
                        conf.cols = [{
                            caption:'签到主题',
                            type:'string',
                        },{
                            caption:'学生学号',
                            type:'string',
                        },{
                            caption:'学生姓名',
                            type:'string',
                        },{
                            caption:'创建时间',
                            type:'string',
                        },{
                            caption:'结束时间',
                            type:'string'
                        },{
                            caption:'签到时间',
                            type:'string'
                        }]; //表格title
                        let rows = [];
                        contentInfo.forEach(item=>{
                           rows.push([signInfo.title,item.user_id,item.user_name,new Date(signInfo.start_time).toLocaleString(),new Date(signInfo.end_time).toLocaleString(),new Date(signInfo.end_time).toLocaleString()]);
                        });
                        conf.rows=rows;//表格内容
                        for (var i = 0; i < 1; i++) {
                            conf = JSON.parse(JSON.stringify(conf));   //clone
                            conf.name = 'sheet'; //不能包含中文
                            confs.push(conf);
                        }
                        let excel = nodeExcel.execute(confs);
                        callbck(excel,signInfo.title);
                    }
                })
            }
        });
    }
};
